source code can be found here

A Python powered Framework for Network Performance Data Analysis-Part 3

Erdem Koç

Visualising a mobile network and KPIs

In Part 2, we focused on "Site/Sector/Cell Level Maps with HTML Pop-Up" using what is already available in folium. There we assumed lowest level of network component is "site", hence ommidirectional circles were enough to represent information.

But any expert who actually dealt with such data would immediately think one further level of detail, which is the direciton of cells of a site.

In more general way a cell can be defined and visualized as below. This note book shows how can folium be used to work on a generic network database which includes coordinate and direction information and possibly many more cell related parameters.

cell

In [1]:
from networklib import networkcon 
import pandas as pd
import math
import folium

Let's connect to our demo database and read artificial network data, with latitude, longitude, azimuth information and many more imaginary parameters, KPIs etc.

In [2]:
nw = networkcon.networkcon("database/network.db")
nw.connect()
2018-07-02 14:42:40.077918 - C:\Users\tcerdkoc\AppData\Local\Continuum\anaconda3\networklib\networkcon.py - conected to db...
2018-07-02 14:42:40.077918 - C:\Users\tcerdkoc\AppData\Local\Continuum\anaconda3\networklib\networkcon.py - fetching queries from C:\Users\tcerdkoc\AppData\Local\Continuum\anaconda3\networklib\queries
In [3]:
network= nw.get_simple("network_query.sql")
network.head()
2018-07-02 14:42:40.124951 - networkcon - running C:\Users\tcerdkoc\AppData\Local\Continuum\anaconda3\networklib\queries\network_query.sql
Out[3]:
Cellid CellName Siteid SiteName Frequency Power Azimuth CellParam1 CellParam2 CellParam3 CellParam4 CellParam5 CellParam6 CellParam7 CellParam8 CellParam9 KPI_x Longitude Latitude
0 4960 Cell_4960 1704 Site_1704 Layer1 42 50 AA 250 549 31 71 68 39 9 11 2.901021 14.583308 52.316767
1 4961 Cell_4961 1704 Site_1704 Layer1 42 120 BA 226 544 30 26 13 59 27 81 2.901021 14.583308 52.316767
2 4962 Cell_4962 1704 Site_1704 Layer1 42 250 CB 424 431 93 92 45 56 41 60 2.867531 14.583308 52.316767
3 5082 Cell_5082 1742 Site_1742 Layer1 42 110 CB 124 177 29 73 98 94 21 12 2.867531 14.342583 52.349111
4 5083 Cell_5083 1742 Site_1742 Layer1 42 220 AA 367 463 39 51 85 10 39 3 2.867531 14.342583 52.349111
In [4]:
network.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 6400 entries, 0 to 6399
Data columns (total 19 columns):
Cellid        6400 non-null object
CellName      6400 non-null object
Siteid        6400 non-null int64
SiteName      6400 non-null object
Frequency     6400 non-null object
Power         6400 non-null int64
Azimuth       6400 non-null int64
CellParam1    6400 non-null object
CellParam2    6400 non-null int64
CellParam3    6400 non-null int64
CellParam4    6400 non-null int64
CellParam5    6400 non-null int64
CellParam6    6400 non-null int64
CellParam7    6400 non-null int64
CellParam8    6400 non-null int64
CellParam9    6400 non-null int64
KPI_x         6400 non-null float64
Longitude     6400 non-null float64
Latitude      6400 non-null float64
dtypes: float64(3), int64(11), object(5)
memory usage: 950.1+ KB

We cannot do trigonometric opoerations directly on latitudes and longitudes, the porportions will be distorted due to obvious fact that especially longitude distances highly depend on latitude. So we will need the information how far 1 degree of latitude and 1 degree of longitude is.

There are several libraries that can do it, but i do not prefer unnecessary dependencies, so I will use a simple distance funcion I found on stackoverflow.

In [5]:
def distance(origin, destination):

    lat1, lon1 = origin
    lat2, lon2 = destination
    radius = 6371  # km

    dlat = math.radians(lat2 - lat1)
    dlon = math.radians(lon2 - lon1)
    a = (math.sin(dlat / 2) * math.sin(dlat / 2) +
         math.cos(math.radians(lat1)) * math.cos(math.radians(lat2)) *
         math.sin(dlon / 2) * math.sin(dlon / 2))
    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a))
    d = radius * c
    
    return d*1000

Now we can define a function that will return the distance between one degree of latitude and longitude, wrt a given coordinate. 0.5's are to go north, sout & east , west one degree in total

In [6]:
def latlonscale(lat, lon):
    return (distance((lat+0.5,lon),(lat-0.5,lon)),distance((lat,lon+0.5),(lat,lon-0.5)))

Ok, now a bit of trigonometry going down here. Basically we are calcuating the coordinates to draw a pizza slice wrt a center, in the direction of azimuth and a with given beamwidth where azimuth is defined as in the figure above.

In [7]:
def drawCell(lat, lon, azimuth, radius, beamw, smooth_factor=1):
    
    # assuming radius is in meters, 
    # this is to make it more smooth as 
    # raidus increases.
    steps = int(radius*smooth_factor*.1)
    
    if steps <= 0:
        steps=1
        
    delta_theta = beamw/steps;
        
    lat_scale, lon_scale = latlonscale(lat,lon)
    vertexes = list()
    # geojson is defined [lat, lon]
    vertexes.append([lon, lat])

    for i in range(0,int(steps)):        
        arcLat = lat+radius*math.sin((azimuth-beamw/2+i*delta_theta)*2*math.pi/360)/lat_scale
        arcLon = lon+radius*math.cos((azimuth-beamw/2+i*delta_theta)*2*math.pi/360)/lon_scale
        vertexes.append([arcLon, arcLat])
        
    return [vertexes]

Just to confirm lets plot some imaginary site with a lot of cells at the center of the earth

In [8]:
m = folium.Map([0, 0], zoom_start=5, tiles=None)

for azimuth in [0, 45, 90, 179, 289, 300]:
    gj = folium.GeoJson(data={'type': 'Polygon', 'coordinates':drawCell(0,0, azimuth, 1e6, 45, smooth_factor=1e-4)})
    gj.add_to(m)

m
Out[8]:

Now, in order to be able to use everything as if we are exporting a geojson file, we will create our own geojson file programatically from network data. Any coloring and style is than exacty the same as in folium.

Below function takes a network dataFrame with minimum 3 columns:

  • Latitude
  • Longitude
  • Azimuth

and every other parameter is opitonal.

In [9]:
def networkToGeoJson(  network
                     , latitude_col="Latitude"
                     , longitude_col="Longitude"
                     , azimuth_col="Azimuth"
                     , smooth_factor=0.1
                     , fillColor="#0000ff"
                     , fillOpacity=0.7
                     , color="#0000ff"
                     , weight=1
                     , radius=300
                     , beamwidth=45):
    
    '''
    returns a dict() that can be converted to a geojson file:
    
    ex: 
        folium.GeoJson(dict())
        
        parameters are the same as in folium
    '''
        
    features=[]

    # for each row of dataFrame
    for key, row in network.iterrows():
        
        properties={}     
        properties["style"] = {
                                'color': color,
                                'fillColor': fillColor,
                                'fillOpacity': fillOpacity,
                                'weight': weight
                              }
        
        # add everything in the dataFrame as property 
        for i in range(0, len(row)):
            properties[network.columns[i]]=row[i]
               
        features.append(
            {   "type" : "Feature",
                "geometry":{ 
                    "type":"Polygon",
                    "coordinates": drawCell(  row[latitude_col]
                            , row[longitude_col]
                            , row[azimuth_col]
                            , row[radius] if isinstance(radius, str) else radius
                            , row[beamwidth] if isinstance(beamwidth, str) else beamwidth
                            , smooth_factor=smooth_factor)
                        },
             
                "properties":properties
            }
        )
    
    if key*smooth_factor>200:
        print('''Warning: There are {} different cells to plot. Folium may not display that many cells. 
        If you do not see the map try decreasing smooth factor <0.1 or number of cells.'''.format(key))

    return { "type": "FeatureCollection", "features": features }

First Let's plot the most simple

In [10]:
m = folium.Map([network.head(1000).Latitude.mean(), network.head(1000).Longitude.mean()], zoom_start=12, tiles="cartodbpositron")
folium.GeoJson(networkToGeoJson(network.head(1000))).add_to(m)
m
Out[10]:

Let's color some cells wrt a "discrete" column.

In [11]:
m = folium.Map([network.head(1000)['Latitude'].mean(), network.head(1000)['Longitude'].mean()], zoom_start=12, tiles="cartodbpositron")

folium.GeoJson(
    networkToGeoJson(network.head(1000), beamwidth=60),
    style_function=lambda feature: {
        'fillColor': 'blue' if 'A' in feature['properties']['CellParam1'] else '#ff0000',
        'color': 'black',
        'weight': 1,
    }
).add_to(m)
m
Out[11]:

Also, it is possible to color every cell wrt o a KPI. For example we can color every cell with a linear legend as a cell is located away from the center of all the cells.

Let's define a linear color map first:

In [12]:
import branca.colormap as cm
In [13]:
linearheat = cm.LinearColormap(['green', 'yellow', 'orange', 'red'],
    vmin=0, vmax=20000, index=[0, 5000, 10000, 20000],  caption='step')

linearheat
Out[13]:
020000
In [14]:
m = folium.Map([network.head(1000)['Latitude'].mean(), network.head(1000)['Longitude'].mean()], zoom_start=12, tiles="cartodbpositron")

folium.GeoJson(
    networkToGeoJson(network.head(1000), beamwidth=60, radius=500),
    style_function=lambda feature: {
        'fillColor': linearheat( 
            distance((network.set_index('CellName')['Latitude'][feature['properties']['CellName']],
                           network.set_index('CellName')['Longitude'][feature['properties']['CellName']]),
                           (network.head(1000)['Latitude'].mean(), network.head(1000)['Longitude'].mean()))),
        'color': 'black',
        'weight': 0,
    }
).add_to(m)
m
Out[14]:

Well, as much as this looks beatiful, it is not very useful for network performance analysis. The reason is that, even if we can distinguish something is wrong with some site, we most probably will want to know which site and also many more parameters related to that site.

Unfortunately, definition of geojson does not include a native pop-up. Therefore, we can only add one pop-up per geojson using folium if we implement a geo-json file as above.

I provided the method above anyway, because it is light-weight and it may be useful as it is for some specific purposes.

Now let's define a similar function, but this time adding html pop-up exploiting featuregroup property of folium.

Note that this function adds every column the dataframe has and pandas.to_html() makes our job very very easy. For less information network dataFrame can be sliced as long as we make sure we have the minimum requirements, namely lat,lon and azimuth.

In [15]:
def networkToGeoJsonPopup(  network
                            , layername = "untitled layer"
                            , latitude_col="Latitude"
                            , longitude_col="Longitude"
                            , azimuth_col="Azimuth"
                            , smooth_factor=0.1
                            , radius=300
                            , beamwidth=45
                            , popup=True
                            , style_function=None
                            , highlight_function=None):
    
    '''
    returns a FeatureGroup() that can be added to a 
    folium map:
    
    ex: feature_group.add_to(m)
        
        parameters are the same as in folium
    '''
     
    feature_group = folium.FeatureGroup(name=layername)    

    # for each row of dataFrame
    for key, row in network.iterrows():
                             
        gj = folium.GeoJson(data={'type': 'Polygon'
                    , 'coordinates': drawCell(  row[latitude_col]
                    , row[longitude_col]
                    , row[azimuth_col]
                    , row[radius] if isinstance(radius, str) else radius
                    , row[beamwidth] if isinstance(beamwidth, str) else beamwidth
                    , smooth_factor=smooth_factor)
                    , network.columns[0] : row[network.columns[0]]
                                 } 
                    , style_function=style_function
                    , highlight_function=highlight_function).add_to(feature_group)
        
        if popup:
            gj.add_child(folium.Popup(network.loc[network[network.columns[0]]==\
                                row[network.columns[0]]].transpose().to_html()))
    
    if key*smooth_factor>200:
        print('''Warning: There are {} different cells to plot. Folium may not display that many cells. 
        If you do not see the map try decreasing smooth factor <0.1 or number of cells.'''.format(key))

    return feature_group
In [16]:
m = folium.Map([network.head(500)['Latitude'].mean(), network.head(500)['Longitude'].mean()], zoom_start=12, tiles="cartodbpositron")

networkToGeoJsonPopup(network.head(500)).add_to(m)
m
Out[16]:

And finally, below is a a piece of code on how to use different colormaps for formatting the cells

In [18]:
from folium import plugins

m = folium.Map([network.head(100)['Latitude'].median(), network.head(100)['Longitude'].median()], zoom_start=13, tiles="cartodbpositron")

# a timing advance layer
networkToGeoJsonPopup(network.head(100), radius=1500, layername="timing advance", style_function=lambda feature: {
        'color': 'black',
        'weight': 1,
        'dashArray': '2, 5',
    "fillOpacity": 0
    
    }, popup=False).add_to(m)

# a kpi layer
networkToGeoJsonPopup(network.head(100), radius="CellParam3", layername="kpi layer", style_function=lambda feature: {
        'fillColor': linearheat( 
            distance((network.set_index(network.columns[0])['Latitude'][feature["geometry"][network.columns[0]]],
                        network.set_index(network.columns[0])['Longitude'][feature["geometry"][network.columns[0]]]),
                           (network.head(100)['Latitude'].mean(), network.head(50)['Longitude'].mean()))),
        'color': 'black',
        'weight': 1,
    "fillOpacity":1
    }).add_to(m)

folium.LayerControl().add_to(m)
m
Out[18]: